﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Web;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Entities;
using Eacademy.Domain.Models;
using Eacademy.Library.Helpers;
using Elmah;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Eacademy.Domain.Repository
{
    public class MessageRepository : IMessageRepository
    {
        EacademyDB db = new EacademyDB();

        public bool CreateMessage(StudentMessagesViewModel studentMessagesViewModel)
        {
            try
            {
                MessagesConversation messagesConversation = new MessagesConversation();
                messagesConversation.Recipients = studentMessagesViewModel.RecipientIdList.Split(',').Length;
                messagesConversation.Title = studentMessagesViewModel.Subject;
                messagesConversation.UserId = studentMessagesViewModel.UserId;
                messagesConversation.Modified = DateTime.Now;
                messagesConversation.Save();

                MessagesMessage message = new MessagesMessage();
                message.Title = studentMessagesViewModel.Subject;
                message.Date = DateTime.Now;
                message.Body = studentMessagesViewModel.Message;
                message.UserId = studentMessagesViewModel.UserId;
                message.ConversationId = messagesConversation.ConversationId;
                message.Save();

                MessagesRecipient myMessage = new MessagesRecipient();
                myMessage.ConversationId = messagesConversation.ConversationId;
                myMessage.InboxMessageId = 0;
                myMessage.InboxRead = true;
                myMessage.InboxDeleted = true;
                myMessage.OutboxMessageId = message.MessageId;
                myMessage.OutboxUpdated = DateTime.Now;
                myMessage.OutboxDeleted = false;
                myMessage.UserId = studentMessagesViewModel.UserId;
                myMessage.Save();

                var recipientIdList = studentMessagesViewModel.RecipientIdList.Split(',').ToList();


                foreach (string recipientStringId in recipientIdList)
                {

                    MessagesRecipient messagesRecipient = new MessagesRecipient();
                    messagesRecipient.ConversationId = messagesConversation.ConversationId;
                    messagesRecipient.InboxMessageId = message.MessageId;
                    messagesRecipient.InboxUpdated = DateTime.Now;
                    messagesRecipient.InboxRead = true;
                    messagesRecipient.InboxDeleted = false;
                    messagesRecipient.OutboxMessageId = 0;
                    messagesRecipient.OutboxDeleted = true;


                    int recipientId = 0;
                    Int32.TryParse(recipientStringId, out recipientId);

                    if (recipientId < 1)
                        continue;
                    messagesRecipient.UserId = recipientId;
                    messagesRecipient.Save();
                }
                return true;

            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public MessageViewModel GetSubjectByConversationId(int id)
        {
            MessageViewModel messageViewModel = new MessageViewModel();
            try
            {
                MessagesConversation messagesConversation =
                    db.Select.From<MessagesConversation>().Where(MessagesConversationsTable.ConversationIdColumn)
                        .IsEqualTo(id).ExecuteSingle<MessagesConversation>();
                messageViewModel.Subject = messagesConversation.Title;
            }
            catch (Exception ex)
            {

                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return messageViewModel;
        }

        public bool ReplyMessage(StudentMessagesViewModel studentMessagesViewModel)
        {
            try
            {

                MessagesMessage message = new MessagesMessage();
                message.Date = DateTime.Now;
                message.Body = studentMessagesViewModel.Message;
                message.UserId = studentMessagesViewModel.UserId;
                message.ConversationId = studentMessagesViewModel.ConversationId;
                message.Save();

                List<MessagesRecipient> messsagesRecipient = db.Select.From<MessagesRecipient>().Where(MessagesRecipientsTable.ConversationIdColumn)
                    .IsEqualTo(studentMessagesViewModel.ConversationId).ExecuteTypedList<MessagesRecipient>();


                foreach (MessagesRecipient recipient in messsagesRecipient)
                {
                    recipient.InboxMessageId = message.MessageId;
                    recipient.InboxUpdated = DateTime.Now;
                    recipient.InboxRead = true;
                    recipient.InboxDeleted = false;
                    recipient.Save();
                }
                return true;

            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public List<MessageViewModel> GetConversationsForUserId(int userid, int pageIndex, int pageSize, out int totalCount, bool count = true)
        {
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<MessageViewModel> messageViewList = new List<MessageViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT ms.*,");
                sql.AppendLine(@"       mm.body,");
                sql.AppendLine(@"       mc.title,");
                sql.AppendLine(@"       mm.user_id,");
                sql.AppendLine(@"       COALESCE(m.firstname, s.firstname,'User')   AS firstname,");
                sql.AppendLine(@"       COALESCE(m.middlename, s.middlename,'') AS middlename,");
                sql.AppendLine(@"       COALESCE(m.lastname, s.lastname,'Deleted')     AS lastname");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   messages_recipients ms");
                sql.AppendLine(@"       LEFT JOIN messages_messages mm");
                sql.AppendLine(@"         ON mm.message_id = ms.inbox_message_id");
                sql.AppendLine(@"       LEFT JOIN messages_conversations mc");
                sql.AppendLine(@"         ON mc.conversation_id = mm.conversation_id");
                sql.AppendLine(@"       LEFT JOIN mentors m");
                sql.AppendLine(@"         ON m.user_id = mm.user_id");
                sql.AppendLine(@"       LEFT JOIN students s");
                sql.AppendLine(@"         ON s.user_id = mm.user_id");
                sql.AppendLine(@"WHERE  ms.user_id = ?UserId");
                sql.AppendLine(@"       AND inbox_message_id > 0");
                sql.AppendLine(@"       AND inbox_deleted = 0  ");


                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?UserId", DbType.Int32, userid);
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                messageViewList = studentsTable.AsEnumerable().Select(datarow => new MessageViewModel()
                {
                    ConversationId = datarow["conversation_id"].ToInteger(),
                    DateSent = datarow["inbox_updated"].ToDate(),
                    Message = datarow["body"].ToString(),
                    NameOfSender = String.Format("{0} {1}", datarow["firstname"], datarow["lastname"]),
                    Unread = datarow["inbox_read"].ToBooleaan(),
                    Subject = datarow["title"].ToString()

                }).ToList();

                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }


                return messageViewList;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return messageViewList;
        }

        public List<MessageViewModel> GetMessagesForConversationId(int conversationId)
        {
            //totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<MessageViewModel> messageViewList = new List<MessageViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendLine(@"SELECT mm.*,");
                sql.AppendLine(@"       COALESCE(m.firstname, s.firstname,'User')   AS firstname,");
                sql.AppendLine(@"       COALESCE(m.middlename, s.middlename,'') AS middlename,");
                sql.AppendLine(@"       COALESCE(m.lastname, s.lastname,'Deleted')     AS lastname");
                sql.AppendLine(@"FROM   messages_messages mm");
                sql.AppendLine(@"       LEFT JOIN mentors m");
                sql.AppendLine(@"         ON m.user_id = mm.user_id");
                sql.AppendLine(@"       LEFT JOIN students s");
                sql.AppendLine(@"         ON s.user_id = mm.user_id");
                sql.AppendLine(@"WHERE  conversation_id = ?ConversationId");
                sql.AppendLine(@"ORDER  BY DATE  ");



                //if (pageSize > 0)
                //{
                //    //control page size
                //    pageSize = pageSize > 10 ? 10 : pageSize;

                //    sql.AddStartRemove();
                //    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                //    sql.AddEndRemove();
                //}

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?ConversationId", DbType.Int32, conversationId);
                //db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                //db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                messageViewList = studentsTable.AsEnumerable().Select(datarow => new MessageViewModel()
                {
                    ConversationId = datarow["conversation_id"].ToInteger(),
                    DateSent = datarow["date"].ToDate(),
                    Message = datarow["body"].ToString(),
                    NameOfSender = String.Format("{0} {1}", datarow["firstname"], datarow["lastname"]),
                    Subject = datarow["title"].ToString()

                }).ToList();

                //if (count)
                //{
                //    string countQuery = sql.ToString().GenerateCountQuery();
                //    comm.CommandText = countQuery;
                //    ds = factory.ExecuteQuery(comm);
                //    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                //}


                return messageViewList;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return messageViewList;
        }
    }
}